package com.bw.dao;

import com.bw.domain.Sugar;
import com.bw.domain.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT b.`name`,sum(o.final_total_amount) value FROM order_info o\n" +
            "INNER JOIN base_province b ON  o.province_id = b.id\n" +
            "GROUP BY b.name ORDER BY value desc")
    List<Sugar> get1();

    @Select("SELECT t.name,  sum(o.final_total_amount)  value FROM order_info o\n" +
            "INNER JOIN order_detail d ON  o.id = d.order_id\n" +
            "INNER JOIN sku_info s ON  d.sku_id = s.id\n" +
            "INNER JOIN base_trademark t ON  s.tm_id = t.id\n" +
            "GROUP BY t.name ORDER BY value DESC")
    List<Sugar> get2();

    @Select("SELECT  c1.`name`,sum(o.final_total_amount)value FROM order_info o\n" +
            "INNER JOIN order_detail d ON  o.id = d.order_id\n" +
            "INNER JOIN sku_info s ON  d.sku_id = s.id\n" +
            "INNER JOIN base_category3 c3 ON  s.category3_id = c3.id\n" +
            "INNER JOIN base_category2  c2 ON  c3.category2_id = c2.id\n" +
            "INNER JOIN base_category1  c1 ON  c2.category1_id = c1.id\n" +
            "GROUP BY c1.`name` ORDER BY value desc")
    List<Sugar> get3();

    @Select("SELECT a.a value1,b.b value2 FROM\n" +
            "(SELECT count(*) 'a' FROM user_info WHERE TIMESTAMPDIFF(DAY,create_time,NOW())<14)a,\n" +
            "(SELECT count(*) 'b' FROM user_info WHERE TIMESTAMPDIFF(DAY,create_time,NOW())>=14)b")
    List<User> get4();
}
